package model.handler.databaseHandling;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.sqlite.SQLiteConfig;

import enums.Property;
import enums.Status;
import model.Configuration;
import model.data.tableModel.TableControl;
import model.data.tableModel.person.Customer;
import model.handler.fileHandling.FileHandler;
import model.invoicing.DateHandler;

/**
 * Handles all main interactions with the SQLite database.
 * <p>
 * Sets the database up and initializes all data.
 * 
 * @version %I%, %G%
 */
public final class DatabaseHandler {

	private static Logger log = LogManager.getLogger("ERROR");
	private static Logger debug = LogManager.getLogger("DEBUG");

	private static Connection conn;
	private static DatabaseHandler instance;

	/**
	 * Only constructor of this class.
	 */
	private DatabaseHandler() {
		go();
	}

	/**
	 * Gets the instance of the DatabaseHandler.
	 * 
	 * @return Only instance of DatabaseHandler
	 */
	public static DatabaseHandler getInstance() {
		if (instance == null) {
			instance = new DatabaseHandler();
		}
		return instance;
	}
	
	public static void initialize(){
		getInstance();
	}

	/**
	 * Builds a <code>Connection</code> to the database.
	 * 
	 * @return <code>Connection</code> to the database
	 */
	public static Connection getConnection() throws SQLException {

		if (conn == null || conn.isClosed()) {			
			SQLiteConfig config = new SQLiteConfig();  
	        config.enforceForeignKeys(true);

			conn = DriverManager.getConnection("jdbc:sqlite:" + Configuration.DATABASENAME, config.toProperties());
		}
		return conn;
	}

	/**
	 * Invokes the commands for starting up the database.
	 * <p>
	 * Is invoked each time the application is launched/the DatabaseHandler is created.
	 */
	private void go() {

		File f = new File(System.getProperty("user.dir") + System.getProperty("file.separator") + Configuration.DATABASENAME);
		if (!f.isFile()) {
			installDatabase();
		}
		initializeData();
	}

	/**
	 * Reads all data from database and creates corresponding objects.
	 */
	private void initializeData() {
		TableControl.setCentralCounter(Integer.valueOf(QueryFactory.getProperty(Property.COUNTER)));
		TableControl.setBillCounter(Integer.valueOf(QueryFactory.getProperty(Property.BILLCOUNTER)));
		TableControl.setPurchaseCounter(Integer.valueOf(QueryFactory.getProperty(Property.PURCHASECOUNTER)));
		QueryFactory.initializeStaff();
		QueryFactory.initializeProducts();
		List<Customer> customerList = QueryFactory.initializeCustomer();
		initializeBills(customerList);
	}
	
	/**
	 * Gets the billID for each Customer.
	 * <p>
	 * If a bill is already saved in the database for the month for the customer, the billID is set to the customer otherwise a new bill is created and written to the database
	 * @param customerList
	 */
	private static void initializeBills(List<Customer> customerList) {
		for (Customer c : customerList) {
			String date = DateHandler.formatMonthDateToString(new Date());
			int billID = QueryFactory.getBillID(c.getCustomerID(), date);
			if (billID == -1) {
				billID = TableControl.getBillCounter();
				QueryFactory.writeBillToDB(false, billID, null, date, c.getCustomerID(), Status.TOCALCULATE, -1);
			}
			c.setBillID(billID);
			TableControl.getInstance().getCustomerMap().put(c.getCustomerID(), c.getName(), c);
		}
		debug.debug("Bills initialized");
	}

	/**
	 * Creates the database.
	 * <p>
	 * Should only be invoked once. Creates all tables by the given .ini file and inserts the initial values to the database.
	 */
	private static void installDatabase() {
		try {
			Connection conn = DatabaseHandler.getConnection();
			Statement s = null;
			s = conn.createStatement();

			for (String str : FileHandler.readFromIni(Configuration.DBINI)) {
				s.addBatch(str);
			}

			int[] status = s.executeBatch();
			debug.debug("Installation of the database ended with status: " +status);
			s.close();
			conn.close();

		} catch (SQLException e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}
	}
}
